'''
@Author: B.N
@Date: 2018-12-01 12:12:31
@LastEditors: B.N
@LastEditTime: 2018-12-02 11:13:20
@Description: stu表信息操作类 
'''

import pymysql


#定义stu表信息操作类
class stu_db_operat():

    #连接数据库
    def __init__(self):
        #获取数据库连接
        self.stu_info_db = pymysql.connect(host="localhost", user="root", password="", db="stu_info_db", charset="utf8")
        #创建游标对象
        self.cursor = self.stu_info_db.cursor()

    #查询所有学生信息
    def findAllInfo(self):

        try:
        #执行sql操作
            self.cursor.execute("select * from stu")
            print("Number of results：", self.cursor.rowcount)

            stu_info_list = self.cursor.fetchall()
            for stu_info in stu_info_list:
                print(stu_info)

        except Exception as error:
            print("system error! Error code：", error)

    #删除学生信息
    def deleInfo(self, id):

        try:
            #执行sql操作
            stu_info = self.cursor.execute("delete from stu where id=%d" % id)
            # 事务提交
            self.stu_info_db.commit()
            print("Delete information successfully：", stu_info)
    
        except Exception as error:
            #事务回滚
            self.stu_info_db.rollback()
            print("system error! Error code：：", error)

    #添加学生信息
    def insertInfo(self, name, age, sex, classid):

        try:
            #执行sql操作
            stu_info = self.cursor.execute("insert into stu(name,age,sex,classid) values('%s','%d','%s','%s')" % (name,age,sex,classid))
            # 事务提交
            self.stu_info_db.commit()
            
            print("Insert information succesfully：", stu_info)
        except Exception as error:
            #事务回滚
            self.stu_info_db.rollback()
            print("system error! Error code：：", error)

    #关闭数据库连接
    def __del__(self):
        self.stu_info_db.close()
